﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using KVB.Common;

using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Configuration; 
using System.Collections;
using Microsoft.CSharp;

namespace Customer_Profile.eCard
{
    public partial class PayConvert : System.Web.UI.Page
    {
        string filename = "";
        DataSet ds = new DataSet();
        string tempFileName = "";
        string tempEFilePath = "";
        string tempEFileXPath = "/eCard/TempPayTemplate"; //EXCEL临时文件保存服务器虚拟存放路径 
        protected void Page_Load(object sender, EventArgs e)
        {
            
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            lblDownLoad.Text = "";
            filename = fileupdown();
            hidFileName.Value = filename;
            fillGrid(filename, "Data");
            ToExcel(ds);
            lblDownLoad.Text = "<a href=" + KVB.Common.Configuration.GetKeyValue("DownFile", "WebURL") + tempEFileXPath + " target=_black>下载</a>";
        }

        public string fileupdown()
        {
            if (Path.GetExtension(FileUpload1.FileName) == ".txt" || Path.GetExtension(FileUpload1.FileName) == ".xlsx" || Path.GetExtension(FileUpload1.FileName) == ".tsv")
            {
                string filename = FileUpload1.FileName;  //FileUpload1.PostedFile.FileName.Substring(FileUpload1.PostedFile.FileName.LastIndexOf("\\"));
                FileUpload1.PostedFile.SaveAs(Server.MapPath("fileupdown\\") + filename);//上传文件
                FileUpload1.PostedFile.InputStream.Close();
                lblWarning.Text = "数据转换成功";
                //Response.Write("<script>alert('数据上传成功！');</script>");
                return filename;
            }
            else
            {
                Response.Write("<script language='Javascript'>alert('请选择txt xlsx tsv文件！');history.go(-1);</script>");
                return null;
            }
        }

        public void fillGrid(string filename, string excelName)
        {
            string conn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + Server.MapPath("fileupdown\\") + filename + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此连接可以操作.xls与.xlsx文件  
            string sqlin = "SELECT * FROM [" + excelName + "$]";
            OleDbCommand olecommand = new OleDbCommand(sqlin, new OleDbConnection(conn));
            OleDbDataAdapter adapterin = new OleDbDataAdapter(olecommand);
            //DataSet dsin = new DataSet();
            adapterin.Fill(ds);
            //this.ASPxGridView1.DataSource = ds.Tables[0];
            //this.ASPxGridView1.DataBind();
            //ASPxGridViewExporter1.WriteXlsToResponse();
        }
     
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
        private void ToExcel(DataSet ds)
        {
           
            string excelTemplateDPath; //EXCEL模板默认服务器物理存放路径 
             
            Microsoft.Office.Interop.Excel.Application app = null; //EXCEL对象 
            Workbooks workbooks; //工作簿集合 
            _Workbook workbook; //当前工作簿 
            Sheets sheets; //SHEET页集合 
            _Worksheet worksheet; //当前SHEET页 

            #region 读取配置文件中路线模板路径及名称并验证是否存在 //获取配置文件中路线模板路径及名称
            excelTemplateDPath = KVB.Common.Configuration.GetKeyValue("DownFile", "ExcelTemplatePath");
            tempEFilePath = KVB.Common.Configuration.GetKeyValue("DownFile", "TempTemplatePath");
            excelTemplateDPath = excelTemplateDPath +"PayTemplate2003.xls";
            #region//验证EXCEL模板文件是否存在
            if (!System.IO.File.Exists(excelTemplateDPath)) 
            {             
                KVB.Common.Log.WriteDefaultLog("模板", "没找到模板");
            } 
            #endregion//验证EXCEL临时文件夹是否存在

            if (!System.IO.File.Exists(tempEFilePath))
            { 
                Directory.CreateDirectory(tempEFilePath); 
            }
            #endregion #region 从配置文件中取列名为填充数据做准备
          
            try
            {
                #region 启动excel进程并加载模板
                //启动EXCEL进程 
                app = new Microsoft.Office.Interop.Excel.Application();
                if (app == null) 
                {
                    lblWarning.Text = "失败！Excel没有启动。";
                    return;
                } 
                app.Visible = false;
                app.UserControl = true;
                app.DisplayAlerts = false;
                //加载读取模板 
                workbooks = app.Workbooks;
                workbook = workbooks.Add(excelTemplateDPath);
                sheets = workbook.Worksheets;
                worksheet = (_Worksheet)sheets.get_Item(1);
                #endregion

                #region 填充数据
                int iRow = 0;
                decimal iPayLimit = decimal.Parse(KVB.Common.Configuration.GetKeyValue("DownFile", "PayLimit").ToString());
                for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
                {
                    decimal dAmount = decimal.Parse(ds.Tables[0].Rows[i][5].ToString());

                    if (dAmount >= 50000)
                    {
                        
                        int iSplitCount =Convert.ToInt16(Math.Ceiling(dAmount / 50000));
                        if (dAmount > iPayLimit)
                        {
                            lblWarning.Text = "数据中有金额大于限额" + iPayLimit.ToString();
                            //lblDownLoad.Text = "";
                            //return;
                        }
                        else
                        {
                            for (int iSplit = 1; iSplit <= iSplitCount; iSplit++)
                            {

                                worksheet.Cells[iRow + 2, 1] = (iRow + 1).ToString();
                                worksheet.Cells[iRow + 2, 2] = ds.Tables[0].Rows[i][4].ToString().Trim();                 //银行账号
                                worksheet.Cells[iRow + 2, 3] = ds.Tables[0].Rows[i][0].ToString().Trim();                 //开户名
                                worksheet.Cells[iRow + 2, 4] = ds.Tables[0].Rows[i][1].ToString().Trim();                 //开户行
                                worksheet.Cells[iRow + 2, 5] = ds.Tables[0].Rows[i][2].ToString().Trim();                 //分行
                                worksheet.Cells[iRow + 2, 6] = ds.Tables[0].Rows[i][3].ToString().Trim();                 //支行
                                worksheet.Cells[iRow + 2, 7] = "私";                                                      //帐户类型	
                                if (iSplit < iSplitCount)
                                {
                                    worksheet.Cells[iRow + 2, 8] = 50000;                                                      //金额	
                                }
                                else
                                {
                                    worksheet.Cells[iRow + 2, 8] = dAmount - 50000 * (iSplitCount - 1);
                                }
                                worksheet.Cells[iRow + 2, 9] = "CNY";                                                     //币种	
                                worksheet.Cells[iRow + 2, 10] = ds.Tables[0].Rows[i][7].ToString().Trim();                //省	
                                worksheet.Cells[iRow + 2, 11] = ds.Tables[0].Rows[i][8].ToString().Trim();                //市	 
                                iRow++;
                            }
                        }
                    }
                    else
                    {
                        worksheet.Cells[iRow + 2, 1] = (iRow + 1).ToString();
                        worksheet.Cells[iRow + 2, 2] = ds.Tables[0].Rows[i][4].ToString().Trim();                 //银行账号
                        worksheet.Cells[iRow + 2, 3] = ds.Tables[0].Rows[i][0].ToString().Trim();                 //开户名
                        worksheet.Cells[iRow + 2, 4] = ds.Tables[0].Rows[i][1].ToString().Trim();                 //开户行
                        worksheet.Cells[iRow + 2, 5] = ds.Tables[0].Rows[i][2].ToString().Trim();                 //分行
                        worksheet.Cells[iRow + 2, 6] = ds.Tables[0].Rows[i][3].ToString().Trim();                 //支行
                        worksheet.Cells[iRow + 2, 7] = "私";                                                      //帐户类型	
                        worksheet.Cells[iRow + 2, 8] = dAmount;                                                  //金额
                        worksheet.Cells[iRow + 2, 9] = "CNY";                                                     //币种	
                        worksheet.Cells[iRow + 2, 10] = ds.Tables[0].Rows[i][7].ToString().Trim();                //省	
                        worksheet.Cells[iRow + 2, 11] = ds.Tables[0].Rows[i][8].ToString().Trim();                //市	 
                        iRow++;
                    }
                }
                #endregion

                #region 保存临时文件
                worksheet.Columns.AutoFit(); //设置自动列宽 
                try
                {
                    tempFileName = "NetPay" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                    tempEFilePath += tempFileName; 
                    tempEFileXPath += "/" + tempFileName;
                    worksheet.SaveAs(tempEFilePath, Missing.Value, Missing.Value, Missing.Value,
                            Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange,
                            Missing.Value, Missing.Value, Missing.Value);
                    DeleteTempFiles(tempEFilePath); //清除临时文件 
                }
                catch(Exception ex) 
                {
                    lblWarning.Text += "失败！excel保存出错！";
                    lblDownLoad.Text = "";
                    KVB.Common.Log.WriteDefaultLog("Excel保存出错",ex.ToString());
                    return;
                }                
                #endregion
            }
            catch (Exception ex)
            {
                lblWarning.Text +="转换失败：" + ex.ToString();
                lblDownLoad.Text = "";
                KVB.Common.Log.WriteDefaultLog("填充数据",ex.ToString());
            }
            finally
            {
                ExcelDispose(app);
            }
        }
        /// /// 关闭Excel进程 /// /// /// /// 
        protected void ExcelDispose(_Application CurExcel)
        {
            try
            {
                if (CurExcel != null)
                {
                    CurExcel.Workbooks.Close();
                    CurExcel.Quit();

                    #region 强行杀死打开的Excel进程
                    IntPtr excelIPtr = new IntPtr(CurExcel.Hwnd); //得到这个句柄，具体作用是得到这块内存入口 
                    int proID = 0;
                    GetWindowThreadProcessId(excelIPtr, out proID); //得到本进程唯一标志k 
                    System.Diagnostics.Process pro = System.Diagnostics.Process.GetProcessById(proID); //得到对进程k的引用 pro.Kill(); 
                    //关闭进程k #endregion CurExcel = null; 
                    #endregion
                }
            }
            catch //(Exception ex) 
            {
                //ShowMessage("在释放内存时发生错误：" + ex.ToString());
            }
        }
        /// /// 删除之前无用的临时文件 /// /// 
        protected void DeleteTempFiles(string iFileName)
        {
            FileInfo f = new FileInfo(iFileName);
            DirectoryInfo dir = f.Directory;
            foreach (FileInfo tfile in dir.GetFiles())
            {
                try { tfile.Delete(); }
                catch { }
            }
        }
    }
}